package org.abratuhi.uradar.modules;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

import org.abratuhi.uradar.model.URadarModule;
import org.abratuhi.uradar.model.URadarModuleModel;

/**
 * URadarModule for Administration and Management of URadar users and URadar modules.
 * <p>
 * Contains 2 tables:
 * <ul>
 * 	<li>uradar_users(uradarid, uradarpasswd)
 * 	<li>uradar_tables(uradar_tablename, uradar_tablegroup)
 * </ul>
 * @author Alexei Bratuhin
 * @version 1.00
 */
public class URadarModuleRegistration extends URadarModule{

	/**
	 * Constructor.
	 * <p>
	 * Here are the tables defined, and corresponding MySQL statements run.
	 * @param name				not important, is substituted with 'base' in constructor
	 * @param description		not important, is substituted with 'Module containing list of all user and all tables in system' in constructor
	 * @param props				module specific properties, currently not really used
	 * @param connection		initialized connection to MySQL DB
	 */
	public URadarModuleRegistration(String name, String description,
			Properties props) {
		// super
		super("base", "Module containing list of all user and all tables in system", props);
		// add registration module specific models
		// tables list
		String model_tablelist_name = new String("uradar_tables");
		String[] model_tablelist_fields = {"uradar_tablename", "uradar_tablegroup"};
		String[] model_tablelist_types = {"varchar(100)", "varchar(100)"};
		URadarModuleModel model_tablelist = new URadarModuleModel(model_tablelist_name, model_tablelist_fields, model_tablelist_types);
		// users list
		String model_users_name = new String("uradar_users");
		String[] model_users_fields = {"uradarid", "uradarpasswd"};
		String[] model_users_types = {"varchar(100)", "varchar(100)"};
		URadarModuleModel model_users = new URadarModuleModel(model_users_name, model_users_fields, model_users_types);
		// add
		ArrayList<URadarModuleModel> modelz = new ArrayList<URadarModuleModel>();
		modelz.add(model_tablelist);
		modelz.add(model_users);
		this.setModel(modelz);
		// create tables if needed
		if(!this.checkTables()) this.createTables();

	}

	@Override
	public String proceedRequest(Properties reqprops) {
		openConnection();
		
		String reqtype = reqprops.getProperty("reqtype");
		if(reqtype.equals("addupdate_user")){
			return addupdateUser(reqprops);
		}
		if(reqtype.equals("remove_user")){
			return removeUser(reqprops);
		}
		
		return INVALID_REQUEST_ID;
	}

	/**
	 * Add/Update user information.
	 * <p>
	 * Notice: currently only as addUser() used, since there is yet no need to update user data.
	 * @param reqprops			module-specific information sent with the query
	 * @return					<ul><li>OK, if successfully added user info</li>
	 * 							<li>FAIL, otherwise</li></ul>
	 */
	public String addupdateUser(Properties reqprops){
		try{
			// get information from request
			String myURadarID = reqprops.getProperty("uradarid");
			String myURadarPasswd = reqprops.getProperty("uradarpasswd");
			// check whether user with this login is already registered, meaning
			// 		login present in db
			//		login<->password don't match
			Connection connection = openConnection();
			Statement stmt1 = connection.createStatement();
			String sql_check_id = "select * from uradar_users where uradarid='"+myURadarID+"';";
			ResultSet rs_check_id = stmt1.executeQuery(sql_check_id);
			if(rs_check_id.first()){	// uradarid is already in use
				stmt1.close();
				closeConnection(connection);
				return FAIL;
			}
			else{	// uradarid is free to use
				// create statement
				Statement stmt = connection.createStatement();
				// generate query string
				String sql_addupdate_user = "insert into uradar_users(uradarid, uradarpasswd) values ('"+myURadarID+"', '"+myURadarPasswd+"');";
				// execute query
				stmt.executeUpdate(sql_addupdate_user);
				// close statement, garbage collector is not to be relied upon
				stmt.close();
				
				closeConnection(connection);
				// return 
				return OK;
			}
		}catch (SQLException e){
			e.printStackTrace();
			// extra return statement for db error cases
			return FAIL;
		}
	}
	
	/**
	 * Check whether user with URadaraID is already registered in DB.
	 * <p>
	 * Purpose: avoid non-unique URadarIDs in DB.
	 * @param reqprops		HTTP GET query
	 * @return 				<ul><li>USER_FAIL, if login not present yet</li>
	 * 						<li>USER_OK, if login already present, but password doesn't match</li>
	 * 						<li>USERPASS_OK, if login already present and password match</li>
	 * 						<li>FAIL, if connection to DB couldn't be established</li></ul>
	 */
	public String checkUser(Properties reqprops){
		try{
			// get information from request
			String myURadarID = reqprops.getProperty("uradarid");
			String myURadarPasswd = reqprops.getProperty("uradarpasswd");
			// check whether user with this login is already registered, meaning
			// 		login present in db
			//		login<->password don't match
			Connection connection = openConnection();
			Statement stmt1 = connection.createStatement();
			String sql_check_id = "select * from uradar_users where (uradarid='"+myURadarID+"' and uradarpasswd='"+myURadarPasswd+"');";
			ResultSet rs_check_id = stmt1.executeQuery(sql_check_id);
			if(rs_check_id.first()){	// uradarid is already in use
				stmt1.close();
				closeConnection(connection);
				return USERPASS_OK;
			}
			else{
				Statement stmt2 = connection.createStatement();
				String sql_check_id2 = "select * from uradar_users where (uradarid='"+myURadarID+"');";
				ResultSet rs_check_id2 = stmt1.executeQuery(sql_check_id2);
				if(rs_check_id2.first()){	// uradarid is already in use
					stmt2.close();
					closeConnection(connection);
					return PASS_FAIL;
				}
				else{
					stmt2.close();
					closeConnection(connection);
					return USER_FAIL;
				}
			}
		}catch (SQLException e){
			e.printStackTrace();
			return FAIL;
		}
	}

	/**
	 * Remove user from users of this module, meaning unregister the user  from URadar.
	 * @param reqprops		HTTP GET query
	 * @return				<ul><li>OK, if deletion successful</li>
	 * 						<li>FAIL, if connection to DB couldn't be established</li></ul>
	 */
	public String removeUser(Properties reqprops){
		// try to connect to DB for search
		try{
			// get info
			String myURadarID = reqprops.getProperty("uradarid");
			
			Connection connection = openConnection();
			// create statement
			Statement stmt = connection.createStatement();
			// generate query strings
			String passwd = reqprops.getProperty("passwd");
			String sql_delete_user = "delete  from "+this.name+" where uradarid='"+myURadarID+"'and uradadpasswd='"+passwd+"';";	// clean <modulname> table
			// query DB for own info
			stmt.executeUpdate(sql_delete_user);
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);
			// return
			return OK;
		} catch (SQLException e){
			e.printStackTrace();
			// return
			return FAIL;
		}
	}
	
	@Override
	public String resolveModuleID(Properties reqprops) {
		return reqprops.getProperty("uradarid");
	}

}
